﻿Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Collections.ObjectModel

Partial Class App_Test_a
    Inherits MyUI.PageBaseApp

    <Services.WebMethod()> _
    Public Shared Function aaa(ByVal k As String) As String

        Return "ciao"


    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        aaa()
        Exit Sub



        Dim q = DB.TAB.Query.GetByPK(32)

        MyApp.Email.System.Queued(MyApp.Email.System.Type.ACCOUNT_ADD_TREND, True, New With {.account_id = 1})



        Exit Sub

        '------------------------------------------------
        ' crea l'ordine
        Dim piano = 4
        Dim promo = 0
        Dim mesi = 0

        Dim order = New mDB.User_Order With {
            .User_Id = CurrUser.Id, .Amount = CurrUser.GetpriceUpgrade(mesi, piano, promo), .Create_Data = Now, .Plan_Id = piano, .Plan_Month = mesi,
            .Promo_Id = promo
        }
        DB.TAB.User_Order.InsertOnSubmit(order)
        DB.SubmitChanges()
        order.Process()

    End Sub

    Public Sub aaa()
        Const OUTPUTFILENAME As String = "E:\Glouk\Dev\trunk\Media\TimeZoneInfo_en.txt"


        Dim timeZones As ReadOnlyCollection(Of TimeZoneInfo) = TimeZoneInfo.GetSystemTimeZones()
        Dim sw As IO.StreamWriter = New IO.StreamWriter(OUTPUTFILENAME, False)

        For Each timeZone As TimeZoneInfo In timeZones
            Dim hasDST As Boolean = timeZone.SupportsDaylightSavingTime
            Dim offsetFromUtc As TimeSpan = timeZone.BaseUtcOffset
            Dim adjustRules() As System.TimeZoneInfo.AdjustmentRule
            Dim offsetString As String

            sw.WriteLine("ID: {0}", timeZone.Id)
            sw.WriteLine("   Display Name: {0, 40}", timeZone.DisplayName)
            sw.WriteLine("   Standard Name: {0, 39}", timeZone.StandardName)
            sw.Write("   Daylight Name: {0, 39}", timeZone.DaylightName)
            sw.Write(IIf(hasDST, "   ***Has ", "   ***Does Not Have "))
            sw.WriteLine("Daylight Saving Time***")
            offsetString = String.Format("{0} hours, {1} minutes", offsetFromUtc.Hours, offsetFromUtc.Minutes)
            sw.WriteLine("   Offset from UTC: {0, 40}", offsetString)
            adjustRules = timeZone.GetAdjustmentRules()
            sw.WriteLine("   Number of adjustment rules: {0, 26}", adjustRules.Length)
            If adjustRules.Length > 0 Then
                sw.WriteLine("   Adjustment Rules:")
                For Each rule As TimeZoneInfo.AdjustmentRule In adjustRules
                    Dim transTimeStart As TimeZoneInfo.TransitionTime = rule.DaylightTransitionStart
                    Dim transTimeEnd As TimeZoneInfo.TransitionTime = rule.DaylightTransitionEnd

                    sw.WriteLine("      From {0} to {1}", rule.DateStart, rule.DateEnd)
                    sw.WriteLine("      Delta: {0}", rule.DaylightDelta)
                    If Not transTimeStart.IsFixedDateRule Then
                        sw.WriteLine("      Begins at {0:t} on {1} of week {2} of {3}", transTimeStart.TimeOfDay, _
                                                                                      transTimeStart.DayOfWeek, _
                                                                                      transTimeStart.Week, _
                                                                                      MonthName(transTimeStart.Month))
                        sw.WriteLine("      Ends at {0:t} on {1} of week {2} of {3}", transTimeEnd.TimeOfDay, _
                                                                                      transTimeEnd.DayOfWeek, _
                                                                                      transTimeEnd.Week, _
                                                                                      MonthName(transTimeEnd.Month))
                    Else
                        sw.WriteLine("      Begins at {0:t} on {1} {2}", transTimeStart.TimeOfDay, _
                                                                       transTimeStart.Day, _
                                                                       MonthName(transTimeStart.Month))
                        sw.WriteLine("      Ends at {0:t} on {1} {2}", transTimeEnd.TimeOfDay, _
                                                                     transTimeEnd.Day, _
                                                                     MonthName(transTimeEnd.Month))
                    End If
                Next
            End If
        Next
        sw.Close()
    End Sub

    ' Given a document name and text, 
    ' inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
    Public Function InsertText(ByVal docName As String, ByVal text As String)
        ' Open the document for editing.
        Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

        Using (spreadSheet)
            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            'Dim shareStringPart As SharedStringTablePart

            'If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
            '    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
            'Else
            '    shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
            'End If

            '' Insert the text into the SharedStringTablePart.
            'Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

            ' Insert a new worksheet.
            'Dim worksheetPart As WorksheetPart = InsertWorksheet(spreadSheet.WorkbookPart)

            Dim worksheetPart As WorksheetPart = spreadSheet.WorkbookPart.WorksheetParts(0)

            ' Insert cell A1 into the new worksheet.
            Dim cell As Cell = InsertCellInWorksheet("C", 1, worksheetPart)

            ' Set the value of cell A1.
            cell.CellValue = New CellValue("zzzzz")
            cell.DataType = New EnumValue(Of CellValues)(CellValues.String) '( Of CellValues)(CellValues.SharedString)

            ' Save the new worksheet.
            worksheetPart.Worksheet.Save()

            Return 0
        End Using

    End Function
    Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
        ' If the part does not contain a SharedStringTable, create one.
        If (shareStringPart.SharedStringTable Is Nothing) Then
            shareStringPart.SharedStringTable = New SharedStringTable
        End If

        Dim i As Integer = 0

        ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If (item.InnerText = text) Then
                Return i
            End If
            i = (i + 1)
        Next

        ' The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
        shareStringPart.SharedStringTable.Save()

        Return i
    End Function

    Private Function InsertWorksheet(ByVal workbookPart As WorkbookPart) As WorksheetPart
        ' Add a new worksheet part to the workbook.
        Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
        newWorksheetPart.Worksheet = New Worksheet(New SheetData)
        newWorksheetPart.Worksheet.Save()
        Dim sheets As Sheets = workbookPart.Workbook.GetFirstChild(Of Sheets)()
        Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

        ' Get a unique ID for the new sheet.
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count() > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max() + 1
        End If

        Dim sheetName As String = ("Sheet" + sheetId.ToString())

        ' Add the new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = sheetName
        sheets.Append(sheet)
        workbookPart.Workbook.Save()

        Return newWorksheetPart
    End Function

    Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
        Dim cellReference As String = (columnName + rowIndex.ToString())

        ' If the worksheet does not contain a row with the specified row index, insert one.
        Dim row As Row
        If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
            row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
        Else
            row = New Row()
            row.RowIndex = rowIndex
            sheetData.Append(row)
        End If

        ' If there is not a cell with the specified column name, insert one.  
        If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
            Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
        Else
            ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Dim refCell As Cell = Nothing
            For Each cell As Cell In row.Elements(Of Cell)()
                If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                    refCell = cell
                    Exit For
                End If
            Next

            Dim newCell As Cell = New Cell
            newCell.CellReference = cellReference

            row.InsertBefore(newCell, refCell)
            worksheet.Save()

            Return newCell
        End If
    End Function

    Private Function insertdb(p As WebApi.Twitter.GeoResponse) As mDB.Place

        If p.result.places.Count = 0 Then
            Return Nothing
        End If

        Dim country = p.result.places(0).country_code
        Dim name = p.result.places(0).name

        Dim TempPlace As mDB.Place ' As WebApi.Twitter.Place = p.result.places(0)
        Dim selectplace As mDB.Place = Nothing
        Dim Primo = True
        For Each place In p.result.places

            TempPlace = insplace(place)

            If Primo Or (place.name = name And place.place_type = "city" And place.country_code = country) Then
                selectplace = TempPlace
                'Exit For
            End If

            Primo = False
        Next

        'Dim selectplace = insplace(TempPlace)

        'Dim Endplace As Boolean
        'Do

        '    Endplace = True

        '    If TempPlace.contained_within IsNot Nothing Then
        '        If TempPlace.contained_within.Count > 0 Then
        '            TempPlace = p.result.places.Where(Function(o) o.id = TempPlace.contained_within(0).id).FirstOrDefault

        '            If TempPlace IsNot Nothing Then
        '                insplace(TempPlace)
        '                Endplace = False
        '            End If
        '        End If

        '    End If


        'Loop Until Endplace

        Return selectplace


    End Function
    Public Function insplace(place As WebApi.Twitter.Place) As mDB.Place

        'Dim p = DB.TAB.Place.BY.Place.PlaceCode(place.id).ToObj

        'If p Is Nothing Then
        '    p = New mDB.Place
        '    DB.TAB.Place.InsertOnSubmit(p)
        'End If

        'p.Place_Code = place.id
        'p.Name = place.name
        'p.Full_Name = place.full_name
        'p.Place_Type = place.place_type
        'p.Country_Code = place.country_code

        'If place.contained_within IsNot Nothing Then
        '    If place.contained_within.Count > 0 Then
        '        Dim pp = insplace(place.contained_within(0))
        '        p.Parent_Place_Id = pp.Id
        '    End If
        'End If

        'DB.SubmitChanges()

        'Return p
        Return New mDB.Place

    End Function


    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click

        'Dim Accounts =
        '    DB.TAB.Account.
        '    REL.Account.User_Account.
        'ToList()

        'For Each Acc In Accounts


        '    Dim q2 = DB.TAB.Account.
        '        REL.Account.Account_Link.
        '        REL.Account.Account_Add_Info.
        '        REL.Account_Add_Info.Place.
        '        BY.Account_Link.Account_Id(Acc.Id, mDB.Account_Link.Type.FOLLOWER).
        '        GroupBy("place.country_code").
        '        SetSelect("place.country_code", "count(place.country_code) as tot")

        '    Dim q = DB.TAB.Place.Where("place_type", "=", "country")


        '    Dim dt = DB.GetQuery.
        '        From(q2, "country_accout", "").
        '        Join(q, "country_code", "country_accout.country_code", "country_place", "").
        '        SetSelect("country_place.id", "country_accout.tot").
        '    ToDataTable()

        '    For Each dr As DataRow In dt.Rows

        '        Acc.SetFollowerAnalysis(mDB.Follower_Analysis.Type.PLACE, dr("id"), dr("tot"))

        '    Next



        'Next

    End Sub

    Protected Sub Button2_Click(sender As Object, e As System.EventArgs) Handles Button2.Click

        'Dim tc = New WebApi.Twitter.TwitterClient(DB.TAB.User_Account.ToList.Item(3).getOauth)

        'Dim accs = DB.TAB.Account.REL.Account.Account_Add_Info.Where("Place_Id", "=", 0).Limit(1000).ToList

        'For Each a In accs

        '    UI.Page.WriteLog(a.Fullname)

        '    a.Account_Add_Info.Place_Id = -1

        '    If a.Location.Trim <> "" Then
        '        Dim p = tc.GetGeoSearch(a.Location)
        '        Dim place = insertdb(p)

        '        If place IsNot Nothing Then
        '            a.Account_Add_Info.Place_Id = place.Id
        '        End If

        '    End If

        '    DB.SubmitChanges()

        'Next

    End Sub

End Class
